-- @owner: zou_jialiang050
-- @date: 2023/4/12
-- @testpoint: 语句添加invoker参数,用户有function权限;调用成功

--step1:创建用户;expect:成功
drop user if exists u_alter_007a cascade;
drop user if exists u_alter_007b cascade;
drop user if exists u_alter_007c cascade;
drop user if exists u_alter_007d cascade;
drop schema if exists sch_alter_007 cascade;
create user u_alter_007a password 'Test@1234';
create user u_alter_007b password 'Test@1234';
create user u_alter_007c sysadmin password 'Test@1234';
create user u_alter_007d sysadmin password 'Test@1234';
create schema sch_alter_007;
grant all on schema sch_alter_007 to u_alter_007a, u_alter_007b, u_alter_007c, u_alter_007d;

--step2:使用普通用户创建function;expect:成功
alter session set session authorization u_alter_007a password 'Test@1234';
drop table if exists sch_alter_007.t_alter_007a;
create table sch_alter_007.t_alter_007a(c1 int);
insert into sch_alter_007.t_alter_007a values(100);
create or replace function sch_alter_007.f_alter_007a()
returns int
as $$
declare
total int;
begin
select * into total from t_alter_007a;
return total;
end;
$$ language plpgsql;
/
alter function sch_alter_007.f_alter_007a sql security invoker;

--step3:使用系统管理员用户调用function;expect:成功
alter session set session authorization default;
alter session set session authorization u_alter_007c password 'Test@1234';
call sch_alter_007.f_alter_007a();

--step4:将权限授予其他普通用户;使用其他普通用户调用function;expect:成功
alter session set session authorization default;
grant u_alter_007a to u_alter_007b;
alter session set session authorization u_alter_007b password 'Test@1234';
call sch_alter_007.f_alter_007a();

--step5:使用系统管理员用户创建function;expect:成功
alter session set session authorization default;
alter session set session authorization u_alter_007c password 'Test@1234';
drop table if exists sch_alter_007.t_alter_007c;
create table sch_alter_007.t_alter_007c(c1 int);
insert into sch_alter_007.t_alter_007c values(100);
create or replace function sch_alter_007.f_alter_007c()
returns int
as $$
declare
total int;
begin
select * into total from t_alter_007c;
return total;
end;
$$ language plpgsql;
/
alter function sch_alter_007.f_alter_007c sql security invoker;

--step6:使用其他系统管理员用户调用function;expect:成功
alter session set session authorization default;
alter session set session authorization u_alter_007d password 'Test@1234';
call sch_alter_007.f_alter_007c();

--step7:将权限授予普通用户;使用普通用户调用function;expect:成功
alter session set session authorization default;
grant u_alter_007c to u_alter_007a;
alter session set session authorization u_alter_007a password 'Test@1234';
call sch_alter_007.f_alter_007c();

--step8:清理环境;expect:成功
alter session set session authorization default;
drop table if exists sch_alter_007.t_alter_007a;
drop table if exists sch_alter_007.t_alter_007c;
drop function if exists sch_alter_007.f_alter_007a;
drop function if exists sch_alter_007.f_alter_007c;
drop user if exists u_alter_007a cascade;
drop user if exists u_alter_007b cascade;
drop user if exists u_alter_007c cascade;
drop user if exists u_alter_007d cascade;
drop schema if exists sch_alter_007 cascade;